package br.com.bbcalibracao.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import br.com.bbcalibracao.bean.Calibracao;
import br.com.bbcalibracao.bean.CalibracaoPdf;

public class CalibracaoDAO {
	
	private Connection connection = null;
	
	public void obtemConexao() throws Exception{
		this.connection = ConnectionFactory.getConexao();
	}
	
	public List<Calibracao> obtemCalibracao(String cnpj){
		PreparedStatement stmt = null;
		ResultSet rs = null;
		List<Calibracao> lista = null;
		try{
			obtemConexao();
			
			final String SQL = "SELECT * FROM CALIBRA_CONTROLE C WHERE CAL_CNPJ = ?";
			
			stmt = connection.prepareStatement(SQL);
			stmt.setString(1, cnpj);
			rs = stmt.executeQuery();
			
			lista = new ArrayList<Calibracao>();
			while (rs.next()) {
				Calibracao cal = new Calibracao();
				
				cal.setId(rs.getInt("CAL_SEQ"));
				cal.setDataCalibracao(rs.getDate("CAL_DAT"));
				cal.setNotaFiscal(rs.getString("CAL_NFE"));
				cal.setNotaFiscalServico(rs.getString("CAL_NFSER"));
				cal.setNomeCliente(rs.getString("CAL_CLINOM"));
				cal.setCnpj(rs.getString("CAL_CNPJ"));
				cal.setTelefone(rs.getString("CAL_TEL"));
				cal.setEmail(rs.getString("CAL_EMA"));
				cal.setCidade(rs.getString("CAL_CID"));
				cal.setUf(rs.getString("CAL_UF"));
				cal.setRegiao(rs.getString("CAL_REGIAO"));
				cal.setBandeira(rs.getString("CAL_BANDEIRA"));
				cal.setLinhaEquipamento(rs.getString("CAL_LINHA"));
				cal.setDescricaoEquipamento(rs.getString("CAL_EQUIPAMENTO"));
				cal.setNumeroSelo(rs.getString("CAL_SELO"));
				cal.setNumeroSerie(rs.getString("CAL_SERIE"));
				cal.setNomeInspetor(rs.getString("CAL_INSPETOR"));
				cal.setPeriodoProximaCalibracao(rs.getInt("CAL_PERIODO"));
				cal.setDataProximaCalibracao(rs.getDate("CAL_PROXIMA"));
				cal.setBsqm(rs.getString("CAL_BSQM"));
				lista.add(cal);
			}
			
		} catch(SQLException ex){
			ex.printStackTrace();
			throw new RuntimeException(ex);
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		} finally{
			ConnectionFactory.closeAll(this.connection, stmt, rs);
		}
		return lista;
	}
	
	public Calibracao obtemCalibracao(int id){
		PreparedStatement stmt = null;
		ResultSet rs = null;
		Calibracao cal = null;
		try{
			obtemConexao();
			
			final String SQL = "SELECT * FROM CALIBRA_CONTROLE C WHERE CAL_SEQ = ?";
			
			stmt = connection.prepareStatement(SQL);
			stmt.setInt(1, id);
			rs = stmt.executeQuery();
			
			cal = new Calibracao();
			while (rs.next()) {
				cal.setId(rs.getInt("CAL_SEQ"));
				cal.setDataCalibracao(rs.getDate("CAL_DAT"));
				cal.setNotaFiscal(rs.getString("CAL_NFE"));
				cal.setNotaFiscalServico(rs.getString("CAL_NFSER"));
				cal.setNomeCliente(rs.getString("CAL_CLINOM"));
				cal.setCnpj(rs.getString("CAL_CNPJ"));
				cal.setTelefone(rs.getString("CAL_TEL"));
				cal.setEmail(rs.getString("CAL_EMA"));
				cal.setCidade(rs.getString("CAL_CID"));
				cal.setUf(rs.getString("CAL_UF"));
				cal.setRegiao(rs.getString("CAL_REGIAO"));
				cal.setBandeira(rs.getString("CAL_BANDEIRA"));
				cal.setLinhaEquipamento(rs.getString("CAL_LINHA"));
				cal.setDescricaoEquipamento(rs.getString("CAL_EQUIPAMENTO"));
				cal.setNumeroSelo(rs.getString("CAL_SELO"));
				cal.setNumeroSerie(rs.getString("CAL_SERIE"));
				cal.setNomeInspetor(rs.getString("CAL_INSPETOR"));
				cal.setPeriodoProximaCalibracao(rs.getInt("CAL_PERIODO"));
				cal.setDataProximaCalibracao(rs.getDate("CAL_PROXIMA"));
				cal.setBsqm(rs.getString("CAL_BSQM"));
			}
			
		} catch(SQLException ex){
			ex.printStackTrace();
			throw new RuntimeException(ex);
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		} finally{
			ConnectionFactory.closeAll(this.connection, stmt, rs);
		}
		return cal;
	}
	
	public List<CalibracaoPdf> obtemCalibracoesPdf(int id){
		PreparedStatement stmt = null;
		ResultSet rs = null;
		List<CalibracaoPdf> lista = null;
		try{
			obtemConexao();
			
			final String SQL = "SELECT * FROM CALIBRA_PDF C WHERE CAL_SEQ = ?";
			
			stmt = connection.prepareStatement(SQL);
			stmt.setInt(1, id);
			rs = stmt.executeQuery();
			
			lista = new ArrayList<CalibracaoPdf>();
			while (rs.next()) {
				CalibracaoPdf cal = new CalibracaoPdf();
				cal.setId(rs.getInt("CAL_SEQ"));
				cal.setNome(rs.getString("CAL_NOM"));
				lista.add(cal);
			}
			
		} catch(SQLException ex){
			ex.printStackTrace();
			throw new RuntimeException(ex);
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		} finally{
			ConnectionFactory.closeAll(this.connection, stmt, rs);
		}
		return lista;
	}
	
	public CalibracaoPdf obtemCalibracaoPdf(int id, String nomeArquivo){
		PreparedStatement stmt = null;
		ResultSet rs = null;
		CalibracaoPdf cal = null;
		try{
			obtemConexao();
			
			final String SQL = "SELECT * FROM CALIBRA_PDF C WHERE CAL_SEQ = ? AND CAL_NOM = ?";
			
			stmt = connection.prepareStatement(SQL);
			stmt.setInt(1, id);
			stmt.setString(2, nomeArquivo);
			rs = stmt.executeQuery();
			
			cal = new CalibracaoPdf();
			while (rs.next()) {
				cal.setId(rs.getInt("CAL_SEQ"));
				cal.setNome(rs.getString("CAL_NOM"));
				cal.setConteudo(rs.getBytes("CAL_PDF"));
			}
			
		} catch(SQLException ex){
			ex.printStackTrace();
			throw new RuntimeException(ex);
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		} finally{
			ConnectionFactory.closeAll(this.connection, stmt, rs);
		}
		return cal;
	}
	
	
}
